﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using Lists;
using GeneralLibs;
using DevExpress.XtraGauges.Win;
using DevExpress.XtraGauges.Win.Gauges.Circular;
using DevExpress.XtraGauges.Core.Model;


namespace BI
{
    public partial class FrmDashboardKPI1 : Form
    {
        public FrmDashboardKPI1()
        {
            InitializeComponent();
        }
        private void Form1_Load(object sender, EventArgs e)
        {
            arcScaleComponent1.Value = 0;
            arcScaleComponent2.Value = 0;
            arcScaleComponent3.Value = 0;
            arcScaleComponent4.Value = 0;

            ucDigitalNumber1.DisplayText = GeneralLibs.CommonLib.Decimal2Money(0);
            ucDigitalNumber2.DisplayText = GeneralLibs.CommonLib.Decimal2Money(0);
            ucDigitalNumber3.DisplayText = GeneralLibs.CommonLib.Decimal2Money(0);
            ucDigitalNumber4.DisplayText = GeneralLibs.CommonLib.Decimal2Money(0);

            digitalGauge1.Text = "0";
            digitalGauge2.Text = "0";
            digitalGauge3.Text = "0";
            digitalGauge4.Text = "0";

            cmbKyBaoCao.SelectedIndex = 17;
            cmbKyBaoCao.SelectedIndex = 18;
        }
        private void PopulateTable()
        {
            BasicForms.FrmProgressBar.StartShowProgress();

            string[] connectionString = new string[3];
            connectionString[0] = "SERVER = 117.6.85.20; DATABASE = HuyHoangCenter; UID= sa; PWD=sqlP2ssw0rd";
            connectionString[1] = "SERVER = 117.6.85.20; DATABASE = ThanhVinh1Center; UID= sa; PWD=sqlP2ssw0rd";
            connectionString[2] = "SERVER = 117.6.85.20; DATABASE = BachKhoaCenter; UID= sa; PWD=sqlP2ssw0rd";
            SqlConnection con1 = new SqlConnection(connectionString[0]);
            SqlConnection con2 = new SqlConnection(connectionString[1]);
            SqlConnection con3 = new SqlConnection(connectionString[2]);
            try
            {
                con1.Open();
                SqlCommand cmd1 = con1.CreateCommand();

                cmd1.CommandText = "SELECT N'Chi nhánh Ngọc Hà' Name, A.Ngay Date , SUM(A.SotienNT) Amount \n" +
                                   "FROM ChungTu A INNER JOIN ChungTu B ON A.ChungTuID = B.ChungTuGocID \n" +
                                   "				INNER JOIN LoaiChungTu C ON C.ID = A.LoaiChungTuID AND C.Ma IN ('HoaDonBanHang', 'HoaDonBanLe') \n" +
                                   "WHERE @ThoiDiemDau <= A.Ngay AND A.Ngay <= @ThoiDiemCuoi \n" +
                                   "GROUP BY A.Ngay \n" +
                                   "ORDER BY A.Ngay ";
                cmd1.Parameters.Clear();
                cmd1.Parameters.AddWithValue("ThoiDiemDau", dtNgayBatDau.DateTime);
                cmd1.Parameters.AddWithValue("ThoiDiemCuoi", dtNgayKetThuc.DateTime);
                SqlDataAdapter da1 = new SqlDataAdapter(cmd1);
                DataTable dt1 = new DataTable();
                da1.Fill(dt1);

                con2.Open();
                SqlCommand cmd2 = con2.CreateCommand();

                cmd2.CommandText = "SELECT N'Chi nhánh Vinh' Name, A.Ngay Date , SUM(A.SotienNT) Amount \n" +
                                   "FROM ChungTu A INNER JOIN ChungTu B ON A.ChungTuID = B.ChungTuGocID \n" +
                                   "				INNER JOIN LoaiChungTu C ON C.ID = A.LoaiChungTuID AND C.Ma IN ('HoaDonBanHang', 'HoaDonBanLe') \n" +
                                   "WHERE @ThoiDiemDau <= A.Ngay AND A.Ngay <= @ThoiDiemCuoi \n" +
                                   "GROUP BY A.Ngay \n" +
                                   "ORDER BY A.Ngay ";
                cmd2.Parameters.Clear();
                cmd2.Parameters.AddWithValue("ThoiDiemDau", dtNgayBatDau.DateTime);
                cmd2.Parameters.AddWithValue("ThoiDiemCuoi", dtNgayKetThuc.DateTime);
                SqlDataAdapter da2 = new SqlDataAdapter(cmd2);
                DataTable dt2 = new DataTable();
                da2.Fill(dt2);

                con3.Open();
                SqlCommand cmd3 = con3.CreateCommand();

                cmd3.CommandText = "SELECT N'Chi nhánh Sài Gòn' Name, A.Ngay Date , SUM(A.SotienNT) Amount \n" +
                                   "FROM ChungTu A INNER JOIN ChungTu B ON A.ChungTuID = B.ChungTuGocID \n" +
                                   "				INNER JOIN LoaiChungTu C ON C.ID = A.LoaiChungTuID AND C.Ma IN ('HoaDonBanHang', 'HoaDonBanLe') \n" +
                                   "WHERE @ThoiDiemDau <= A.Ngay AND A.Ngay <= @ThoiDiemCuoi \n" +
                                   "GROUP BY A.Ngay \n" +
                                   "ORDER BY A.Ngay ";
                cmd3.Parameters.Clear();
                cmd3.Parameters.AddWithValue("ThoiDiemDau", dtNgayBatDau.DateTime);
                cmd3.Parameters.AddWithValue("ThoiDiemCuoi", dtNgayKetThuc.DateTime);
                SqlDataAdapter da3 = new SqlDataAdapter(cmd3);
                DataTable dt3 = new DataTable();
                da3.Fill(dt3);

                foreach (DataRow row in dt1.Rows)
                {
                    string name = row[0].ToString();
                    DateTime date = (DateTime)row[1];
                    decimal amount = (decimal)row[2];
                    dataSet1.Tables["Data"].Rows.Add(new object[] { name, date, amount });
                }

                foreach (DataRow row in dt2.Rows)
                {
                    string name = row[0].ToString();
                    DateTime date = (DateTime)row[1];
                    decimal amount = (decimal)row[2];
                    dataSet1.Tables["Data"].Rows.Add(new object[] { name, date, amount });
                }

                foreach (DataRow row in dt3.Rows)
                {
                    string name = row[0].ToString();
                    DateTime date = (DateTime)row[1];
                    decimal amount = (decimal)row[2];
                    dataSet1.Tables["Data"].Rows.Add(new object[] { name, date, amount });
                }

                fieldDate1.GroupInterval = DevExpress.XtraPivotGrid.PivotGroupInterval.DateMonth;
                fieldValue1.Visible = false;
                pivotGridControl1.OptionsView.ShowRowTotals = false;
                pivotGridControl1.OptionsView.ShowRowGrandTotals = false;
                fieldDate.CollapseAll();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                con1.Close();
                con2.Close();
                con3.Close();
            }
        }

        private void pivotGridControl1_CustomCellValue(object sender, DevExpress.XtraPivotGrid.PivotCellValueEventArgs e)
        {
            if (ReferenceEquals(e.DataField, fieldVar))
            {
                decimal oldValue = Convert.ToDecimal(e.Value);
                if (oldValue == 0)
                    e.Value = 0;
                else if (oldValue < 0)
                    e.Value = -1;
                else
                    e.Value = 1;
            }

        }

        decimal _TongVonDauTu = 0, _TongLai = 0;
        private void Calculate(string connectString)
        {
            //Kiểm tra xem nếu xem từ đầu kỳ cuối thì phải tính cả tồn đầu kỳ còn các trường hợp khác thì chỉ tính phát sinh.
            bool xemTuDauKyCuoi = false;

            BasicForms.FrmProgressBar.StartShowProgress();
            SqlConnection con = new SqlConnection(connectString);
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED";
                cmd.ExecuteNonQuery();

                DateTime dtPointer = dtNgayBatDau.DateTime;
                Guid taiKhoanLai = (Guid)DanhMucCauHinhTaiKhoanMacDinh.Instance.GetTaiKhoanIDByTinhChat("DoanhThuBanHangVaCungCapDichVu");

                Guid taiKhoanDoanhThuID = (Guid)DanhMucCauHinhTaiKhoanMacDinh.Instance.GetTaiKhoanIDByTinhChat("DoanhThuBanHangVaCungCapDichVu");
                //ucDigitalNumberDoanhSo.DisplayText = GeneralLibs.CommonLib.Decimal2Money(TaiKhoan.TinhSoPhatSinhCo(cmd, taiKhoanDoanhThuID, dtNgayBatDau.DateTime, dtNgayKetThuc.DateTime));
                decimal tongChi = NhatKyThuChi.TongChi(cmd, dtNgayBatDau.DateTime, dtNgayKetThuc.DateTime);

                //Chi chia làm 5 loại.
                //Chi khác 811, 635
                DanhMucTaiKhoan dmTKChiKhac811_635 = DanhMucTaiKhoan.Filter("SoHieu IN ('811','635')");
                decimal tongChiKhac811_635 = NhatKyThuChi.TongChiTheoTaiKhoanDoiUng(cmd, dtNgayBatDau.DateTime, dtNgayKetThuc.DateTime, dmTKChiKhac811_635);
                //Chi phí hoạt động và sản xuất 641 . 642
                Guid taiKhoanChiPhiBanHangID = (Guid)DanhMucTaiKhoan.Instance.GetBySoHieu("641").ID;
                Guid taiKhoanChiPhiQuanLyID = (Guid)DanhMucTaiKhoan.Instance.GetBySoHieu("642").ID;
                Guid taiKhoanLuongID = (Guid)DanhMucTaiKhoan.Instance.GetBySoHieu("334").ID;
                SoDuTaiKhoan soDuTaiKhoanChiPhiBanHang = new SoDuTaiKhoan();
                SoDuTaiKhoan soDuTaiKhoanChiPhiQuanLy = new SoDuTaiKhoan();
                SoDuTaiKhoan soDuTaiKhoanLuong = new SoDuTaiKhoan();
                if (xemTuDauKyCuoi)
                {
                    soDuTaiKhoanChiPhiBanHang = SoDuTaiKhoan.CalculateFromDB(dtNgayKetThuc.DateTime, taiKhoanChiPhiBanHangID);
                    soDuTaiKhoanChiPhiQuanLy = SoDuTaiKhoan.CalculateFromDB(dtNgayKetThuc.DateTime, taiKhoanChiPhiQuanLyID);
                    soDuTaiKhoanLuong = SoDuTaiKhoan.CalculateFromDB(dtNgayKetThuc.DateTime, taiKhoanLuongID);
                }
                else
                {
                    soDuTaiKhoanChiPhiBanHang = SoDuTaiKhoan.CalculatePhatSinhTrongKy(dtNgayBatDau.DateTime, dtNgayKetThuc.DateTime, taiKhoanChiPhiBanHangID);
                    soDuTaiKhoanChiPhiQuanLy = SoDuTaiKhoan.CalculatePhatSinhTrongKy(dtNgayBatDau.DateTime, dtNgayKetThuc.DateTime, taiKhoanChiPhiQuanLyID);
                    soDuTaiKhoanLuong = SoDuTaiKhoan.CalculatePhatSinhTrongKy(dtNgayBatDau.DateTime, dtNgayKetThuc.DateTime, taiKhoanLuongID);
                }

                decimal chiPhiHoatDong = soDuTaiKhoanChiPhiBanHang.DuNo + soDuTaiKhoanChiPhiQuanLy.DuNo + soDuTaiKhoanLuong.DuNo;

                Guid taiKhoanChiPhiSXID = (Guid)DanhMucTaiKhoan.Instance.GetBySoHieu("213").ID;
                SoDuTaiKhoan soDuTaiKhoanChiPhiSX = new SoDuTaiKhoan();
                if (xemTuDauKyCuoi)
                {
                    soDuTaiKhoanChiPhiSX = SoDuTaiKhoan.CalculateFromDB(dtNgayKetThuc.DateTime, taiKhoanChiPhiSXID);
                }
                else
                {
                    soDuTaiKhoanChiPhiSX = SoDuTaiKhoan.CalculatePhatSinhTrongKy(dtNgayBatDau.DateTime, dtNgayKetThuc.DateTime, taiKhoanChiPhiSXID);
                }

                //Chi nguồn vốn 411
                //Nguồn vốn 411
                DanhMucTaiKhoan dmTKChiNguonVon411 = DanhMucTaiKhoan.Filter("SoHieu = '411'");
                decimal tongChiNguonVon411 = NhatKyThuChi.TongChiTheoTaiKhoanDoiUng(cmd, dtNgayBatDau.DateTime, dtNgayKetThuc.DateTime, dmTKChiNguonVon411);

                //Chi phí còn lại là chi phí mua hàng
                decimal tongChiPhiMuaHang = tongChi - tongChiKhac811_635 - soDuTaiKhoanChiPhiBanHang.DuNo - soDuTaiKhoanChiPhiQuanLy.DuNo;


                decimal laiGop = 0;
                //Tính lãi
                cmd.CommandText = "SELECT SUM(Value7) AS DoanhSoBan, SUM(Value3) AS GiaVon, SUM(Value7 - Value3) AS Lai\n" +
                                        "FROM KhoanMucChungTu \n" +
                                        "WHERE ChungTuID IN (SELECT ChungTuID FROM ChungTu  \n" +
                                        "					WHERE ChungTuGocID IN (SELECT ChungTuID \n" +
                                        "											FROM ChungTu \n" +
                                        "											WHERE LoaiChungTuID IN (SELECT ID FROM LoaiChungTu \n" +
                                        "																	WHERE Ma IN (N'HoaDonBanHang', N'HoaDonBanLe') \n" +
                                        "																	) \n" +
                                        "                                                    AND @ThoiDiemDau <= Ngay AND Ngay <= @ThoiDiemCuoi AND GhiSo = 1\n" +
                                        "											)\n" +
                                        "                   )\n" +
                                        " AND Value5 IS NOT NULL AND Value3 IS NOT NULL ";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("ThoiDiemDau", dtNgayBatDau.DateTime);
                cmd.Parameters.AddWithValue("ThoiDiemCuoi", dtNgayKetThuc.DateTime);

                decimal giaVon = 0;

                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    try
                    {
                        if (dr["Lai"] != DBNull.Value)
                        {
                            laiGop = (decimal)dr["Lai"];
                            giaVon = (decimal)dr["GiaVon"];
                        }
                    }
                    catch
                    {
                    }
                }
                dr.Close();
                decimal tongChiPhi = chiPhiHoatDong + soDuTaiKhoanChiPhiSX.DuNo + tongChiKhac811_635;

                decimal tongVonDauTu = giaVon + tongChiPhi;

                switch (connectString)
                {
                    case "SERVER = 117.6.85.20; DATABASE = HuyHoangCenter; UID= sa; PWD=sqlP2ssw0rd":
                        _TongVonDauTu += tongVonDauTu;
                        _TongLai += laiGop;
                        ucDigitalNumber1.DisplayText = GeneralLibs.CommonLib.Decimal2Money(laiGop);
                        digitalGauge1.Text = CommonLib.Decimal2Money(tongVonDauTu);
                        arcScaleComponent1.Value = (float)(laiGop / tongVonDauTu) * 100;
                        break;
                    case "SERVER = 117.6.85.20; DATABASE = ThanhVinh1Center; UID= sa; PWD=sqlP2ssw0rd":
                        _TongVonDauTu += tongVonDauTu;
                        _TongLai += laiGop;
                        ucDigitalNumber2.DisplayText = GeneralLibs.CommonLib.Decimal2Money(laiGop);
                        digitalGauge2.Text = CommonLib.Decimal2Money(tongVonDauTu);
                        arcScaleComponent2.Value = (float)(laiGop / tongVonDauTu) * 100;
                        break;
                    case "SERVER = 117.6.85.20; DATABASE = BachKhoaCenter; UID= sa; PWD=sqlP2ssw0rd":
                        _TongVonDauTu += tongVonDauTu;
                        _TongLai += laiGop;
                        ucDigitalNumber3.DisplayText = GeneralLibs.CommonLib.Decimal2Money(laiGop);
                        digitalGauge3.Text = CommonLib.Decimal2Money(tongVonDauTu);
                        arcScaleComponent3.Value = (float)(laiGop / tongVonDauTu) * 100;
                        break;
                    default:
                        break;
                }
            }
            catch (Exception ex)
            {
                BasicForms.FrmExceptionNotifier.ShowAndLog(ex);
            }
            finally
            {
                con.Close();
                BasicForms.FrmProgressBar.FinishShowProgress();
            }
        }

        private void cmbKyBaoCao_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                DateTime ngayBatDau = dtNgayBatDau.DateTime;
                DateTime ngayKetThuc = dtNgayBatDau.DateTime;
                DateTime now = GeneralLibs.CommonLib.GetSystemDateTime();
                switch (cmbKyBaoCao.Text)
                {
                    case "Hôm nay":
                        dtNgayBatDau.Enabled = true;
                        dtNgayKetThuc.Enabled = true;
                        ngayBatDau = new DateTime(now.Year, now.Month, now.Day, 0, 0, 0);
                        ngayKetThuc = new DateTime(now.Year, now.Month, now.Day, 23, 59, 59);
                        break;
                    case "Tuần gần nhất":
                        dtNgayBatDau.Enabled = false;
                        dtNgayKetThuc.Enabled = false;
                        ngayBatDau = new DateTime(now.Year, now.Month, now.Day, 0, 0, 0).AddDays(-7);
                        ngayKetThuc = new DateTime(now.Year, now.Month, now.Day, 23, 59, 59);
                        break;
                    case "Tháng này":
                        dtNgayBatDau.Enabled = false;
                        dtNgayKetThuc.Enabled = false;
                        ngayBatDau = new DateTime(now.Year, now.Month, 1, 0, 0, 0);
                        ngayKetThuc = new DateTime(now.Year, now.Month, System.DateTime.DaysInMonth(now.Year, now.Month), 23, 59, 59);
                        break;
                    case "Tùy chọn":
                        dtNgayBatDau.Enabled = true;
                        dtNgayKetThuc.Enabled = true;
                        ngayBatDau = new DateTime(GeneralLibs.Config.NamTaiChinh, 1, 1);
                        ngayKetThuc = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 23, 59, 59);
                        break;
                    case "Cả năm":
                        dtNgayBatDau.Enabled = false;
                        dtNgayKetThuc.Enabled = false;
                        ngayBatDau = new DateTime(GeneralLibs.Config.NamTaiChinh, 1, 1);
                        ngayKetThuc = new DateTime(GeneralLibs.Config.NamTaiChinh, 12, 31, 23, 59, 59);
                        break;
                    case "Quý I":
                        dtNgayBatDau.Enabled = false;
                        dtNgayKetThuc.Enabled = false;
                        ngayBatDau = new DateTime(GeneralLibs.Config.NamTaiChinh, 1, 1);
                        ngayKetThuc = new DateTime(GeneralLibs.Config.NamTaiChinh, 3, 31, 23, 59, 59);
                        break;
                    case "Quý II":
                        dtNgayBatDau.Enabled = false;
                        dtNgayKetThuc.Enabled = false;
                        ngayBatDau = new DateTime(GeneralLibs.Config.NamTaiChinh, 4, 1);
                        ngayKetThuc = new DateTime(GeneralLibs.Config.NamTaiChinh, 6, 30, 23, 59, 59);
                        break;
                    case "Quý III":
                        dtNgayBatDau.Enabled = false;
                        dtNgayKetThuc.Enabled = false;
                        ngayBatDau = new DateTime(GeneralLibs.Config.NamTaiChinh, 7, 1);
                        ngayKetThuc = new DateTime(GeneralLibs.Config.NamTaiChinh, 9, 30, 23, 59, 59);
                        break;
                    case "Quý IV":
                        dtNgayBatDau.Enabled = false;
                        dtNgayKetThuc.Enabled = false;
                        ngayBatDau = new DateTime(GeneralLibs.Config.NamTaiChinh, 10, 1);
                        ngayKetThuc = new DateTime(GeneralLibs.Config.NamTaiChinh, 12, 31, 23, 59, 59);
                        break;
                    default:
                        try
                        {
                            dtNgayBatDau.Enabled = false;
                            dtNgayKetThuc.Enabled = false;
                            string strThang = ((string)cmbKyBaoCao.SelectedItem).Substring(((string)(cmbKyBaoCao.SelectedItem)).LastIndexOf(" "));
                            int thang = int.Parse(strThang);

                            ngayBatDau = new DateTime(GeneralLibs.Config.NamTaiChinh, thang, 1);
                            ngayKetThuc = new DateTime(GeneralLibs.Config.NamTaiChinh, thang,
                                            DateTime.DaysInMonth(GeneralLibs.Config.NamTaiChinh, thang), 23, 59, 59);
                        }
                        catch (SqlException ex)
                        {
                            MessageBox.Show(ex.Message);
                        }
                        break;
                }
                dtNgayBatDau.DateTime = ngayBatDau;
                dtNgayKetThuc.DateTime = ngayKetThuc;
            }
            catch (Exception ex)
            {
                BasicForms.FrmExceptionNotifier.ShowAndLog(ex);
            }
        }

        private void btnLoad_Click(object sender, EventArgs e)
        {
            try
            {
                //Tính Pivot
                PopulateTable();
                pivotGridControl1.RefreshData();
                pivotGridControl1.BestFit();

                //tính cho gauge
                string[] connectionString = new string[3];
                connectionString[0] = "SERVER = 117.6.85.20; DATABASE = HuyHoangCenter; UID= sa; PWD=sqlP2ssw0rd";
                connectionString[1] = "SERVER = 117.6.85.20; DATABASE = ThanhVinh1Center; UID= sa; PWD=sqlP2ssw0rd";
                connectionString[2] = "SERVER = 117.6.85.20; DATABASE = BachKhoaCenter; UID= sa; PWD=sqlP2ssw0rd";

                foreach (string item in connectionString)
                {
                    Calculate(item);
                }

                ucDigitalNumber4.DisplayText = GeneralLibs.CommonLib.Decimal2Money(_TongLai);
                digitalGauge4.Text = CommonLib.Decimal2Money(_TongVonDauTu);
                arcScaleComponent4.Value = (float)(_TongLai / _TongVonDauTu) * 100;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                BasicForms.FrmProgressBar.FinishShowProgress();
            }
        }
    }
}